Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# NANVL

Syntax

Description of the illustration nanvl.gif

Purpose

The `NANVL` function is useful only for floating-point numbers of type `BINARY_FLOAT` or `BINARY_DOUBLE`. It instructs Oracle Database to return an alternative value `n1` if the input value `n2` is `NaN` (not a number). If `n2` is not `NaN`, then Oracle returns `n2`. This function is useful for mapping `NaN` values to `NULL`.

This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, and returns that datatype.

See Also: Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion, "Floating-Point Numbers" for information on binary-float comparison semantics, and "Numeric Precedence" for information on numeric precedence

Examples

Using table `float_point_demo` created for TO_BINARY_DOUBLE, insert a second entry into the table:

    Insert INTO float_point_demo
      VALUES (0,'NaN','NaN');

    SELECT * FROM float_point_demo;

       DEC_NUM BIN_DOUBLE  BIN_FLOAT
    ---------- ---------- ----------
       1234.56 1.235E+003 1.235E+003
             0        Nan        Nan

The following example returns `bin_float` if it is a number. Otherwise, 0 is returned.

    SELECT bin_float, NANVL(bin_float,0)
      FROM float_point_demo;

     BIN_FLOAT NANVL(BIN_FLOAT,0)
    ---------- ------------------
    1.235E+003         1.235E+003
           Nan                  0